package com.iflysse.dao;

import com.iflysse.pojo.po.common.CommonPoResp;
import com.iflysse.pojo.po.req.AnswerQuestionDetailPoReq;
import com.iflysse.pojo.po.req.TestPoReq;
import com.iflysse.pojo.po.req.TestStatePoReq;
import com.iflysse.pojo.po.resp.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

/**
 * 测试活动
 */
@Repository
public interface TestDao {
    /**
     * 保存test
     * @param testPoReq
     * @return
     */
    @Insert({"INSERT INTO tb_test " +
            "(test_id,is_subject_order,is_option_order,expiration_date,update_time," +
            "test_state,test_title,teacher_id,question_amount,teaching_class_id) " +
            "values " +
            "(#{testId},#{isSubjectOrder},#{isOptionOrder},#{expirationDate},now(),#{state.stateId},#{testTitle},#{teacherId},#{QuestionIdList.size()},#{teachingClassId})" })
    @Options(useGeneratedKeys = true, keyProperty = "test_id")
    int addTest(TestPoReq testPoReq) throws SQLException;

    /**
     * 添加题目到test
     * @param testPoReq
     * @return
     */
    @Insert({
            "<script>",
            "insert into tb_test_question (test_question_id,test_id,question_id) "+
                    "<foreach collection='questionIdList' item='item' index='index' separator=','>"+
                    "(uuid(), #{testId}, #{item}) "+
                    "</foreach>"+
                    "</script>"
    })
    @Options(useGeneratedKeys = true, keyProperty = "test_question_id")
    public int addQuestionToTest(TestPoReq testPoReq) throws SQLException;


    /**
     * 改变测试的状态
     * @param testStatePoReq
     * @return
     * @throws SQLException
     */
    @Update("update tb_test set test_state = #{state.stateId} where test_id =#{testId}")
    int updateTestState(TestStatePoReq testStatePoReq) throws SQLException;

    /**
     * 根据测试编号,删除测试
     * @param testId
     * @return
     */
    @Update("update tb_test set is_delete = 1 where test_id =#{testId}")
    int deleteTestByTestId(String testId) throws SQLException;

    /**
     * 根据教学班级编号列表获取课后练习列表
     *
     * @param teachingClassIds 教学班级编号列表
     * @param studentId 学生编号
     * @return
     */
    @Select({" <script>" +
            "select test_id task_id,test_title task_name,update_time,expiration_date task_expiration_date,test_state task_state,'1' type ,(SELECT count(*) from tb_student_test where student_id = #{studentId} and test_id=tb_test.test_id and test_state =1) student_state from tb_test WHERE is_delete = 0 and teaching_class_id in " +
            " <foreach collection='teachingClassIds' open='(' item='teachingClassId' separator=',' close=')'> #{teachingClassId}</foreach>" +
            "ORDER BY update_time desc"+
            " </script>" })
    @Results({
            @Result(property = "activityId",column = "task_id"),
            @Result(property = "activityTitle",column = "task_name"),
            @Result(property = "updateTime",column = "update_time"),
            @Result(property = "expirationDate",column = "task_expiration_date"),
            @Result(property = "state",column = "task_state"),
            @Result(property = "studentState",column = "student_state"),
    })
    List<TeachingActivityPoResp> getTestByTeachingClassIds(List<String> teachingClassIds, String studentId) throws SQLException;

    /**
     * 根据测试编号,获取测试信息
     * @param testId
     * @return
     */


    @Select("select test_id ,is_subject_order,is_option_order,test_title,teacher_id,update_time,expiration_date,test_state,number_of_complete,teaching_class_id,question_amount  from tb_test WHERE test_id = #{testId}")
    @Results({
            @Result(property = "testId",column = "test_id"),
            @Result(property = "isSubjectOrder",column = "is_subject_order"),
            @Result(property = "isOptionOrder",column = "is_option_order"),
            @Result(property = "testTitle",column = "test_title"),
            @Result(property = "teacherId",column = "teacher_id"),
            @Result(property = "testState",column = "test_state"),
            @Result(property = "publishDate",column = "update_time"),
            @Result(property = "expirationDate",column = "expiration_date"),
            @Result(property = "numberOfComplete",column = "number_of_complete"),
            @Result(property = "teachingClassId",column = "teaching_class_id"),
            @Result(property = "totalQuestionNumber",column = "question_amount"),
})
    TestPoResp getTestByTestId(String testId) throws SQLException;

    /**
     * 根据测试编号和学生编号查询答题次数
     * @param testId
     * @param studentId
     * @return
     */
    @Select("select count(*) from tb_student_test where test_id = #{testId} and student_id = #{studentId}")
    Integer getNumberOfAnswerTestByTestIdAndStudentId(String testId, String studentId) throws SQLException;

    /**
     * 根据测试编号和学生编号查询学生答题记录
     * @param testId
     * @param studentId
     * @return
     */
    @Select("select student_test_id ,do_time,number_correct,total_number,test_time  from tb_student_test WHERE test_id = #{testId} and student_id = #{studentId}")
    @Results({
            @Result(property = "studentTestId",column = "student_test_id"),
            @Result(property = "doTime",column = "do_time"),
            @Result(property = "numberCorrect",column = "number_correct"),
            @Result(property = "totalNumber",column = "total_number"),
            @Result(property = "testTime",column = "test_time"),
    })
    List<StudentAnswerRecordPoResp> getRecordOfTestByTestIdAndStudentId(String testId, String studentId) throws SQLException;


    /**
     * 添加学生参加测试的一次记录
     * @param uuid 主键
     * @param testId 测试编号
     * @param studentId 学生编号
     * @return
     */
    @Insert({"insert into tb_student_test (student_test_id,test_id,student_id,test_time) values (UUID(),#{testId},#{studentId},now())"})
    @Options(useGeneratedKeys = true, keyProperty = "student_test_id")
    int addStudentTest(String uuid, String testId, String studentId) throws SQLException;

    /**
     * 根据学生测试编号,获取该学生该次测试的题目
     * @param uuid
     * @return
     */
    @Select("select * from tb_student_test_detail where student_test_id = #{uuid}")
    @Results({
            @Result(property = "studentTestDetailId",column = "student_test_detail_id"),
            @Result(property = "questionId",column = "question_id"),
            @Result(property = "answer",column = "student_answer"),
    })
    List<AnswerSheetPoResp> getQuestionList4StudentTest(String uuid) throws SQLException;


    /**
     * 根据测试编号,获取本次测试的问题列表
     * @param testId
     * @return
     */
    @Select("select question_id from tb_test_question where test_id = #{testId}")
    List<String> getQuestionList4StudentTestByTestId(String testId) throws SQLException;

    /**
     * 批量插入学生答题详情记录
     * @param answerQuestionDetailPoReqs 回答问题详情实体列表
     * @return
     */
    @Insert({
            "<script>",
            "insert into tb_student_test_detail (student_test_detail_id,student_test_id,question_id,standard_answer) values "+
                    "<foreach collection='answerQuestionDetailPoReqs' item='item' index='index' separator=','>"+
                    "(#{item.studentTestDetailId},#{item.studentTestId},#{item.questionId},#{item.standardAnswer}) "+
                    "</foreach>"+
                    "</script>"
    })
    @Options(useGeneratedKeys = true, keyProperty = "student_test_detail_id")
    int addStudentAnswerQuestDetailRecord(List<AnswerQuestionDetailPoReq> answerQuestionDetailPoReqs) throws SQLException;

    /**
     * 根据学生编号和测试编号,获取未完成的某次学生测试编号
     * @param studentId
     * @param testId
     * @return
     */
    @Select("select student_test_id from tb_student_test where student_id = #{studentId} and test_id = #{testId} and test_state= 0")
    String getUnfinishedTestIdByStudentId(String studentId, String testId) throws SQLException;

    /**
     * 学生答题,修改答题详情的答案即可
     * @param testDetailId
     * @param answer
     * @return
     */
    @Update("update tb_student_test_detail set student_answer = #{answer} where student_test_detail_id = #{testDetailId}")
    int updateAnswerOfTest(String testDetailId, String answer) throws SQLException;

    /**
     * 根据学生测试编号获取学生测试详情
     * @param studentTestId
     * @return
     */
    @Select("select * from tb_student_test where student_test_id = #{studentTestId}")
    @Results({
            @Result(property = "studentTestId",column = "student_test_id"),
            @Result(property = "testId",column = "test_id"),
            @Result(property = "studentId",column = "student_id"),
    })
    StudentTestPoResp getStudentTestByStudentTestId(String studentTestId) throws SQLException;

    /**
     * 更新测试完成人数
     * @param testId
     * @return
     */
    @Update("update tb_test set number_of_complete = number_of_complete + 1 where test_id = #{testId}")
    int updateNumberOfFinishedTest(String testId) throws SQLException;

    /**
     * 更新学生本次考试的状态及正确答题数
     * @param studentTestId
     * @param rightNumber
     * @return
     */
    @Update("update tb_student_test set test_state =  1 , number_correct = #{rightNumber},do_time=TimeStampDiff(MINUTE,test_time,now()) where student_test_id = #{studentTestId}")
    int updateStudentTestState(String studentTestId, int rightNumber) throws SQLException;

    /**
     * 根据学生编号获取该学生已经完成的测试编号列表
     *
     * @param studentId
     * @return
     * @throws SQLException
     */
    @Select("select DISTINCT test_id from tb_student_test where student_id = #{studentId} ")
    List<String> getFinishedTestByStudentId(String studentId) throws SQLException;


    /**
     * 根据班级和已经完成的测试编号,获取未完成的测试信息
     * @param teachingIds
     * @param finishedIds
     * @return
     */
    @Select({" <script>" +
            "select * from tb_test where teaching_class_id in "+
            " <foreach collection='teachingIds' open='(' item='teachingId' separator=',' close=')'> #{teachingId}</foreach>" +
            "<if test='finishedIds.size() > 0'> "+
            ", and test_id not in" +
            " <foreach collection='finishedIds' open='(' item='finishedId' separator=',' close=')'> #{finishedId}</foreach>"+
            "</if>" +
            " </script>" })
    @Results({
            @Result(property = "testId",column = "task_id"),
            @Result(property = "isSubjectOrder",column = "is_subject_order"),
            @Result(property = "isOptionOrder",column = "is_option_order"),
            @Result(property = "testTitle",column = "test_title"),
            @Result(property = "teacherId",column = "teacher_id"),
            @Result(property = "testState",column = "test_state"),
            @Result(property = "publishDate",column = "update_time"),
            @Result(property = "expirationDate",column = "expiration_date"),
            @Result(property = "numberOfComplete",column = "number_of_complete"),
    })
    List<TestPoResp> getUnfinishedTestByTeachingClassIdAndFinishedTestId(List<String> teachingIds, List<String> finishedIds) throws SQLException;


    /**
     * 根据班级和已经完成的测试编号,获取未完成的测试数量
     * @param teachingIds
     * @param finishedIds
     * @return
     */
    @Select({" <script>" +
            "select count(*) from tb_test where teaching_class_id in "+
            " <foreach collection='teachingIds' open='(' item='teachingId' separator=',' close=')'> #{teachingId}</foreach>" +
            "<if test='finishedIds.size() > 0'> "+
            ", and test_id not in" +
            " <foreach collection='finishedIds' open='(' item='finishedId' separator=',' close=')'> #{finishedId}</foreach>"+
            "</if>" +
            " </script>" })
    int getUnfinishedTestAmountByTeachingClassIdAndFinishedTestId(List<String> teachingIds, List<String> finishedIds) throws SQLException;


    /**
     * 根据学生测试编号,获取本次测试的正确答题数
     * @param studentTestId
     * @return
     */
    @Select({"select count(*) from tb_student_test_detail where student_test_id = #{studentTestId} and student_answer = standard_answer" })
    int getRightAnswerNumbersByStudentTestId(String studentTestId) throws SQLException;

    /**
     * 根据测试编号获取测试的平均正确率
     * @param testId
     * @return
     */
    @Select({"select convert(avg(number_correct/total_number)*100,signed) AvgOfAccuracy ,convert(avg(do_time),signed) AvgOfUseTime  from tb_student_test where test_id = #{testId}"})
    Map<String, Object> getAvgAccuracyAndUseTimeByTestId(String testId) throws SQLException;

    /**
     * 根据测试编号获取本次测试所涉及的知识点和知识点编号
     * @param testId
     * @return
     */
    @Select({"SELECT DISTINCT(knowledge_point_id) knowledge_point_id,knowledge_point_name from vw_student_test where test_id = #{testId}"})
    List<Map<String,Object>> getKnowledgePointByTestId(String testId) throws SQLException;

    /**
     * 根据测试编号和知识点编号获取知识点正确率
     * @param testId
     * @param KnowledgePointId
     * @return
     */
    @Select({"select convert((select count(*) from vw_student_test where student_answer <> standard_answer and test_id = #{testId} and knowledge_point_id = #{KnowledgePointId}) / (select count(*) from vw_student_test where test_id = #{testId} and knowledge_point_id = #{KnowledgePointId})*100,signed) accuracy"})
    int getKnowledgePointAccuracyByTestIdAndKnowledgePointId(String testId,String KnowledgePointId) throws SQLException;


    /**
     * 根据学生测试详情查询学生答案
     * @param studentDetailId
     * @return
     * @throws SQLException
     */
    @Select({ "select student_answer from tb_student_test_detail where student_test_detail_id=#{studentDetailId}"})
    String queryStudentAnswerByStudentDetailId(String studentDetailId) throws SQLException;

    /**
     * 根据测试编号获取本次测试的题目数量
     * @param taskId
     * @return
     */
    @Select({"select question_amount from tb_test where test_id=#{taskId}"})

    int getQuestionAmountByTestId(String taskId) throws SQLException;
}
